package com.walker.service.catalogue;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.walker.util.CommonUtils;

@Service
public class CatalogueFieldService {

	@Autowired
	@Qualifier("dmJdbcTemplate")
	private JdbcTemplate dmJdbcTemplate;
	
	/**
	 * 字段列表
	 * @param params
	 * @return
	 */
	public Map<String,Object> queryFieldList(Map<String,Object> params){
		String project_id = CommonUtils.valueOf(params.get("project_id"));
		String cube_id = CommonUtils.valueOf(params.get("cube_id"));
		String field_search = CommonUtils.valueOf(params.get("field_search"));
		String keyword = CommonUtils.valueOf(params.get("keyword"));
		int pageSize = Integer.parseInt(CommonUtils.valueOf(params.get("pageSize")));
		int pageNo = Integer.parseInt(CommonUtils.valueOf(params.get("pageNo")));
		int offset = (pageNo - 1) * pageSize ;
		
		List<Object> paramList = new ArrayList<Object>();
		
		String countSql = getFieldCountSql(paramList,project_id,cube_id,field_search,keyword);
		Object totalRecords = dmJdbcTemplate.queryForMap(countSql,paramList.toArray()).get("count");
		
		String sql = getFieldSql(project_id,cube_id,field_search,keyword);
		paramList.add(pageSize);
		paramList.add(offset);
		List<Map<String,Object>> deriList = dmJdbcTemplate.queryForList(sql,paramList.toArray());
		
		Map<String,Object> data = new HashMap<String,Object>();
		data.put("total_records", totalRecords);
		data.put("field_data", deriList);
		
		return data;
	}

	/**
	 * 项目信息
	 * @return
	 */
	public List<Map<String,Object>> queryProjectInfo(){
		String sql = "select id as project_id,project_code,project_name from t_meta_project";
		return dmJdbcTemplate.queryForList(sql);
	}
	
	/**
	 *表信息
	 * @param project_id
	 * @return
	 */
	public List<Map<String,Object>> queryCubeInfo(String project_id){
		String sql = "select cube_id,cube_code,cube_name from t_meta_cubes";
		List<Object> paramList = new ArrayList<Object>();
		if(StringUtils.isNotBlank(project_id)){
			sql = sql + " where project_id = ?";
			paramList.add(project_id);
		}
		return dmJdbcTemplate.queryForList(sql,paramList.toArray());
	}
	
	/*private String getFieldSql(String project_id,String cube_id,String field_search,String keyword) {
		StringBuffer sql = new StringBuffer();
		sql.append("select sync.element_code,");
		sql.append("       sync.element_name,");
		sql.append("       ele.element_type,");
		sql.append("       bus.busin_name,");
		sql.append("       case when entity_rel.element_id is not null then '是' else '否' end as is_used,");
		sql.append("       ele.oper_user,");
		sql.append("       date_format(ele.oper_time,'%Y-%m-%d %H:%i:%s') as oper_time");
		sql.append("  from t_meta_element ele inner join t_meta_sync_maxc_element sync on ele.element_code = sync.element_code and ele.element_type = sync.element_type");
		sql.append("  left join t_meta_field_relation field_rel");
		sql.append("    on ele.element_id = field_rel.element_id");
		sql.append("  left join t_meta_business_field bus");
		sql.append("    on field_rel.busin_id = bus.busin_id");
		if(StringUtils.isNotBlank(cube_id)){
			sql.append("  inner join (select distinct element_id from t_meta_entity_relation where cube_id = ?) entity_rel");
			sql.append("    on ele.element_id = entity_rel.element_id");
		}else{
			if(StringUtils.isNotBlank(project_id)){
				sql.append(" inner join (select distinct element_id from t_meta_entity_relation where cube_id in (select cube_id from t_meta_cubes where project_id = ?)) entity_rel");
				sql.append("    on ele.element_id = entity_rel.element_id");
			}else{
				sql.append("  left join (select distinct element_id from t_meta_entity_relation) entity_rel");
				sql.append("    on ele.element_id = entity_rel.element_id");
			}
		}
		sql.append(" where 1 = 1");
		if(StringUtils.isNotBlank(field_search)){
			sql.append(" and (sync.element_code like ? or sync.element_name like ?)");
		}
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and (ele.element_code like ? or ele.element_name like ?)");
		}
		sql.append(" order by ele.oper_time desc");
		sql.append(" limit ? offset ? ");
		return sql.toString();
	}*/
	
	private String getFieldSql(String project_id,String cube_id,String field_search,String keyword) {
		StringBuffer sql = new StringBuffer();
		sql.append("select ele.element_code,");
		sql.append("       ele.element_name,");
		sql.append("       ele.element_type,");
		sql.append("       bus.busin_name,");
		sql.append("       case when entity_rel.element_id is not null then '是' else '否' end as is_used,");
		sql.append("       ele.oper_user,");
		sql.append("       date_format(ele.oper_time,'%Y-%m-%d %H:%i:%s') as oper_time");
		sql.append("  from t_meta_element ele");
		sql.append("  left join t_meta_field_relation field_rel");
		sql.append("    on ele.element_id = field_rel.element_id");
		sql.append("  left join t_meta_business_field bus");
		sql.append("    on field_rel.busin_id = bus.busin_id");
		if(StringUtils.isNotBlank(cube_id)){
			sql.append("  inner join (select distinct element_id from t_meta_entity_relation where cube_id = ?) entity_rel");
			sql.append("    on ele.element_id = entity_rel.element_id");
		}else{
			if(StringUtils.isNotBlank(project_id)){
				sql.append(" inner join (select distinct element_id from t_meta_entity_relation where cube_id in (select cube_id from t_meta_cubes where project_id = ?)) entity_rel");
				sql.append("    on ele.element_id = entity_rel.element_id");
			}else{
				sql.append("  left join (select distinct element_id from t_meta_entity_relation) entity_rel");
				sql.append("    on ele.element_id = entity_rel.element_id");
			}
		}
		sql.append(" where 1 = 1");
		if(StringUtils.isNotBlank(field_search)){
			sql.append(" and (ele.element_code like ? or ele.element_name like ?)");
		}
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and (ele.element_code like ? or ele.element_name like ?)");
		}
		sql.append(" order by ele.oper_time desc");
		sql.append(" limit ? offset ? ");
		return sql.toString();
	}
	
	/*private String getFieldCountSql(List<Object> paramList,String project_id,String cube_id,String field_search,String keyword) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(1) count");
		sql.append("  from t_meta_element ele inner join t_meta_sync_maxc_element sync on ele.element_code = sync.element_code and ele.element_type = sync.element_type");
		sql.append("  left join t_meta_field_relation field_rel");
		sql.append("    on ele.element_id = field_rel.element_id");
		sql.append("  left join t_meta_business_field bus");
		sql.append("    on field_rel.busin_id = bus.busin_id");
		
		if(StringUtils.isNotBlank(cube_id)){//选择了表，其对应项目就知道了
			sql.append(" inner join (select distinct element_id from t_meta_entity_relation where cube_id = ?) entity_rel");
			sql.append("    on ele.element_id = entity_rel.element_id");
			paramList.add(cube_id);
		}else{
			if(StringUtils.isNotBlank(project_id)){
				sql.append(" inner join (select distinct element_id from t_meta_entity_relation where cube_id in (select cube_id from t_meta_cubes where project_id = ?)) entity_rel");
				sql.append("    on ele.element_id = entity_rel.element_id");
				paramList.add(project_id);
			}
		}
		sql.append(" where 1 = 1");
		if(StringUtils.isNotBlank(field_search)){
			sql.append(" and (sync.element_code like ? or sync.element_name like ?)");
			paramList.add("%"+field_search+"%");
			paramList.add("%"+field_search+"%");
		}
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and (sync.element_code like ? or sync.element_name like ?)");
			paramList.add("%"+keyword+"%");
			paramList.add("%"+keyword+"%");
		}
		return sql.toString();
	}*/
	
	private String getFieldCountSql(List<Object> paramList,String project_id,String cube_id,String field_search,String keyword) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(1) count");
		sql.append("  from t_meta_element ele");
		sql.append("  left join t_meta_field_relation field_rel");
		sql.append("    on ele.element_id = field_rel.element_id");
		sql.append("  left join t_meta_business_field bus");
		sql.append("    on field_rel.busin_id = bus.busin_id");
		
		if(StringUtils.isNotBlank(cube_id)){//选择了表，其对应项目就知道了
			sql.append(" inner join (select distinct element_id from t_meta_entity_relation where cube_id = ?) entity_rel");
			sql.append("    on ele.element_id = entity_rel.element_id");
			paramList.add(cube_id);
		}else{
			if(StringUtils.isNotBlank(project_id)){
				sql.append(" inner join (select distinct element_id from t_meta_entity_relation where cube_id in (select cube_id from t_meta_cubes where project_id = ?)) entity_rel");
				sql.append("    on ele.element_id = entity_rel.element_id");
				paramList.add(project_id);
			}
		}
		sql.append(" where 1 = 1");
		if(StringUtils.isNotBlank(field_search)){
			sql.append(" and (ele.element_code like ? or ele.element_name like ?)");
			paramList.add("%"+field_search+"%");
			paramList.add("%"+field_search+"%");
		}
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and (ele.element_code like ? or ele.element_name like ?)");
			paramList.add("%"+keyword+"%");
			paramList.add("%"+keyword+"%");
		}
		return sql.toString();
	}
}